Hello all and welcome back to the Excel Tip of the Week! This week, we have a General User post in which we’re taking a fresh new look at these four simple but very useful functions: ROW, COLUMN, ROWS, and COLUMNS.
ROW and COLUMN
These two simple functions both work with the position of cells in Excel itself. They can tell you the row number or column number of a cell:
Note that the column result is numeric even though the default Excel view uses letters to denote each column.
These functions can also just return the row/column that the formula itself is in, by entering them without an input:
These functions are very commonly used for all kinds of applications where a simple counter is needed – for example if you want an invoice number that ticks up with each row, or you want a calendar that counts by rows. Doing this with ROW or COLUMN instead of by adding one to a previous cell means that the numbering behaves better when sorted. If you create a numbering by adding 1 to the previous row’s number, it will break when the data is sorted; however create numbering using ROW and it will not change even if the data is sorted.
However do note that such numberings are affected by adding rows / columns later on. To fix this, if you are going to use ROW for numbering, try to calculate based on the position of the cell relative to the header, not to the worksheet row:
ROWS, COLUMNS
These two functions just count the number of rows / columns in a given reference:
These are most commonly used for a quick count of the size of a named range or Excel Table – either for statistics purposes, or because you need the total in order to compute some subsequent thing.
Just like ROW and COLUMN, these of course can change later on if new rows / columns are inserted in-between the references of the function, so do watch out for that when considering if your approach is future-proof.
In earlier versions of Excel, ROW and friends were sometimes used to generate arrays of numbers for use in array formulas – for example ROW(A1:A10) would generate an array of the numbers 1-10 if confirmed with Ctrl Shift Enter. If you have Office 365 this functionality is now replaced by the SEQUENCE function, but the option does still exist if you are on earlier versions.
Excel community
This article is brought to you by the Excel Community where you can find additional extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Excel with Business.
